﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;

namespace StudentManagementSystem
{
    public class School
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public List<Class> Classes { get; set; }
    }

    public class Class
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public List<Student> Students { get; set; }
    }

    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }

    public class Log
    {
        public int Id { get; set; }
        public DateTime Timestamp { get; set; }
        public string Action { get; set; }
    }

    public class StudentManagementSystem
    {
        private const string ConnectionString = "Data Source=student.db";

        public void CreateSchool(School school)
        {
            using (var connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    var command = connection.CreateCommand();
                    command.Transaction = transaction;

                    command.CommandText = "INSERT INTO School (Name) VALUES (@Name)";
                    command.Parameters.AddWithValue("@Name", school.Name);
                    command.ExecuteNonQuery();

                    school.Id = (int)connection.LastInsertRowId;

                    foreach (var @class in school.Classes)
                    {
                        command.CommandText = "INSERT INTO Class (SchoolId, Name) VALUES (@SchoolId, @Name)";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@SchoolId", school.Id);
                        command.Parameters.AddWithValue("@Name", @class.Name);
                        command.ExecuteNonQuery();

                        @class.Id = (int)connection.LastInsertRowId;

                        foreach (var student in @class.Students)
                        {
                            command.CommandText = "INSERT INTO Student (ClassId, Name, Age) VALUES (@ClassId, @Name, @Age)";
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@ClassId", @class.Id);
                            command.Parameters.AddWithValue("@Name", student.Name);
                            command.Parameters.AddWithValue("@Age", student.Age);
                            command.ExecuteNonQuery();
                        }
                    }

                    transaction.Commit();
                }
            }
        }

        public void UpdateSchool(School school)
        {
            using (var connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    var command = connection.CreateCommand();
                    command.Transaction = transaction;

                    command.CommandText = "UPDATE School SET Name = @Name WHERE Id = @Id";
                    command.Parameters.AddWithValue("@Name", school.Name);
                    command.Parameters.AddWithValue("@Id", school.Id);
                    command.ExecuteNonQuery();

                    foreach (var @class in school.Classes)
                    {
                        if (@class.Id == 0)
                        {
                            command.CommandText = "INSERT INTO Class (SchoolId, Name) VALUES (@SchoolId, @Name)";
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@SchoolId", school.Id);
                            command.Parameters.AddWithValue("@Name", @class.Name);
                            command.ExecuteNonQuery();

                            @class.Id = (int)connection.LastInsertRowId;
                        }
                        else
                        {
                            command.CommandText = "UPDATE Class SET Name = @Name WHERE Id = @Id";
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@Name", @class.Name);
                            command.Parameters.AddWithValue("@Id", @class.Id);
                            command.ExecuteNonQuery();
                        }

                        foreach (var student in @class.Students)
                        {
                            if (student.Id == 0)
                            {
                                command.CommandText = "INSERT INTO Student (ClassId, Name, Age) VALUES (@ClassId, @Name, @Age)";
                                command.Parameters.Clear();
                                command.Parameters.AddWithValue("@ClassId", @class.Id);
                                command.Parameters.AddWithValue("@Name", student.Name);
                                command.Parameters.AddWithValue("@Age", student.Age);
                                command.ExecuteNonQuery();
                            }
                            else
                            {
                                command.CommandText = "UPDATE Student SET Name = @Name, Age = @Age WHERE Id = @Id";
                                command.Parameters.Clear();
                                command.Parameters.AddWithValue("@Name", student.Name);
                                command.Parameters.AddWithValue("@Age", student.Age);
                                command.Parameters.AddWithValue("@Id", student.Id);
                                command.ExecuteNonQuery();
                            }
                        }
                    }

                    transaction.Commit();
                }
            }
        }

        public void DeleteSchool(int schoolId)
        {
            using (var connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    var command = connection.CreateCommand();
                    command.Transaction = transaction;

                    command.CommandText = "DELETE FROM School WHERE Id = @Id";
                    command.Parameters.AddWithValue("@Id", schoolId);
                    command.ExecuteNonQuery();

                    command.CommandText = "DELETE FROM Class WHERE SchoolId = @SchoolId";
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@SchoolId", schoolId);
                    command.ExecuteNonQuery();

                    command.CommandText = "DELETE FROM Student WHERE ClassId IN (SELECT Id FROM Class WHERE SchoolId = @SchoolId)";
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@SchoolId", schoolId);
                    command.ExecuteNonQuery();

                    transaction.Commit();
                }
            }
        }

        public List<School> GetAllSchools()
        {
            var schools = new List<School>();

            using (var connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                var command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM School";
                var reader = command.ExecuteReader();

                while (reader.Read())
                {
                    var school = new School
                    {
                        Id = Convert.ToInt32(reader["Id"]),
                        Name = reader["Name"].ToString(),
                        Classes = new List<Class>()
                    };

                    schools.Add(school);
                }

                reader.Close();

                foreach (var school in schools)
                {
                    command.CommandText = "SELECT * FROM Class WHERE SchoolId = @SchoolId";
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@SchoolId", school.Id);
                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        var @class = new Class
                        {
                            Id = Convert.ToInt32(reader["Id"]),
                            Name = reader["Name"].ToString(),
                            Students = new List<Student>()
                        };

                        school.Classes.Add(@class);
                    }

                    reader.Close();

                    foreach (var @class in school.Classes)
                    {
                        command.CommandText = "SELECT * FROM Student WHERE ClassId = @ClassId";
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@ClassId", @class.Id);
                        reader = command.ExecuteReader();

                        while (reader.Read())
                        {
                            var student = new Student
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString(),
                                Age = Convert.ToInt32(reader["Age"])
                            };

                            @class.Students.Add(student);
                        }

                        reader.Close();
                    }
                }
            }

            return schools;
        }

        public void CreateLog(string action)
        {
            using (var connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                var command = connection.CreateCommand();
                command.CommandText = "INSERT INTO Log (Timestamp, Action) VALUES (@Timestamp, @Action)";
                command.Parameters.AddWithValue("@Timestamp", DateTime.Now);
                command.Parameters.AddWithValue("@Action", action);
                command.ExecuteNonQuery();
            }
        }

        public List<Log> GetAllLogs()
        {
            var logs = new List<Log>();

            using (var connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                var command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM Log";
                var reader = command.ExecuteReader();

                while (reader.Read())
                {
                    var log = new Log
                    {
                        Id = Convert.ToInt32(reader["Id"]),
                        Timestamp = Convert.ToDateTime(reader["Timestamp"]),
                        Action = reader["Action"].ToString()
                    };

                    logs.Add(log);
                }

                reader.Close();
            }

            return logs;
        }
    }

    public class Program
    {
        static void Main(string[] args)
        {
            var system = new StudentManagementSystem();

            // 创建学校、班级、学生，并保存到数据库中
            var school = new School
            {
                Name = "School 1",
                Classes = new List<Class>
                {
                    new Class
                    {
                        Name = "Class 1",
                        Students = new List<Student>
                        {
                            new Student { Name = "Student 1", Age = 10 },
                            new Student { Name = "Student 2", Age = 11 }
                        }
                    },
                    new Class
                    {
                        Name = "Class 2",
                        Students = new List<Student>
                        {
                            new Student { Name = "Student 3", Age = 12 },
                            new Student { Name = "Student 4", Age = 13 }
                        }
                    }
                }
            };

            system.CreateSchool(school);

            // 更新学校、班级、学生信息
            school.Name = "School 1 (Updated)";
            school.Classes[0].Name = "Class 1 (Updated)";
            school.Classes[0].Students[0].Name = "Student 1 (Updated)";
            system.UpdateSchool(school);

            // 删除学校及其相关数据
            system.DeleteSchool(school.Id);

            // 获取所有学校及其相关数据
            var allSchools = system.GetAllSchools();
            foreach (var s in allSchools)
            {
                Console.WriteLine($"School: {s.Name}");
                foreach (var c in s.Classes)
                {
                    Console.WriteLine($"Class: {c.Name}");
                    foreach (var student in c.Students)
                    {
                        Console.WriteLine($"Student: {student.Name}, Age: {student.Age}");
                    }
                }
            }

            // 创建日志记录
            system.CreateLog("Created a new school");
            system.CreateLog("Updated a class");
            system.CreateLog("Deleted a student");

            // 获取所有日志记录
            var allLogs = system.GetAllLogs();
            foreach (var log in allLogs)
            {
                Console.WriteLine($"Log: {log.Timestamp} - {log.Action}");
            }

            Console.ReadLine();
        }
    }
}
